IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID (N'[dbo].[sp_Report_HangTonDuoiDinhMuc]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[sp_Report_HangTonDuoiDinhMuc]
GO


CREATE PROC [sp_Report_HangTonDuoiDinhMuc]
(
	@Ma_chi_nhanh		INT,
	@Ngay_xem			DATETIME
)
AS
BEGIN
	DECLARE @Ngay_xem_convert	NVARCHAR(10)

	SET @Ngay_xem_convert = CONVERT(NVARCHAR, DAY(@Ngay_xem)) + '/' + CONVERT(NVARCHAR, MONTH(@Ngay_xem)) + '/' + CONVERT(NVARCHAR, YEAR(@Ngay_xem))
	
	SELECT A.Ten AS 'Ten_nhom_hang',
		B.Ma_hang AS 'Ma_hang_hoa',
		B.Ten AS 'Ten_hang_hoa',
		F.Don_vi_goc AS 'Don_vi_tinh',
		SUM(CASE 
				WHEN G.Loai_nhap_xuat = 0 AND CONVERT(NVARCHAR,G.Ngay_cap_nhat_cuoi,111) BETWEEN @Ngay_xem AND CONVERT(NVARCHAR,C.Ngay_cap_nhat_cuoi,111) 
				THEN E.So_luong 
				ELSE 0 END) AS 'SL_xuat_den_hien_tai',
		SUM(CASE 
				WHEN G.Loai_nhap_xuat = 4 AND CONVERT(NVARCHAR,G.Ngay_cap_nhat_cuoi,111) BETWEEN @Ngay_xem AND CONVERT(NVARCHAR,C.Ngay_cap_nhat_cuoi,111)  
				THEN E.So_luong 
				ELSE 0 END) AS 'SL_nhap_den_hien_tai',
		B.Ton_dinh_muc AS 'Dinh_muc_ton',
		C.So_luong AS 'SL_hang_ton',
		C.Gia_nhap_cuoi AS 'Gia_nhap',
		@Ngay_xem_convert AS 'Ngay_xem'
	FROM NhomHang AS A INNER JOIN HangHoa AS B ON A.id = B.Ma_nhom_hang 
		INNER JOIN HangTon AS C ON B.id = C.Ma_hang 
		INNER JOIN KhoHang AS D ON C.Ma_kho_hang = D.id 
		INNER JOIN ChiTietNhapXuat AS E ON B.id = E.Ma_hang_hoa 
		INNER JOIN DonViQuiDoi AS F ON B.Ma_don_vi_qui_doi = F.id
		INNER JOIN PhieuNhapXuat AS G ON G.id = E.Ma_phieu_nhap_xuat
	WHERE D.Ma_chi_nhanh = @Ma_chi_nhanh
	GROUP BY A.Ten, B.Ma_hang, B.Ten, F.Don_vi_goc, B.Ton_dinh_muc,C.So_luong, C.Gia_nhap_cuoi
END


